<?php
error_reporting ( E_ALL );
$PageSecurity = 14;
include_once ('includes/session.inc');
set_time_limit ( 0 );

$action = isset ( $_POST ['action'] ) ? $_POST ['action'] : '';

if ($action == 'getPartner') {
    $result = _single ( 'api/partner' )->GetAllPartner ( $_SESSION ['UserID'], $_SESSION ['Password'] );

    $data = '{"success":true';
    $data .= ',"data":[';
    $haveData = false;

    while ( $row = DB_fetch_array ( $result ) ) {
        $haveData = true;
        $data .= sprintf ( '{"partner_id":"%s","partner_number":"%s","partner_name":"%s"},', $row ['partner_id'], $row ['partner_number'], $row ['partner_name'] );
    }

    if ($haveData) {
        $data = substr ( $data, 0, strlen ( $data ) - 1 );
    }

    $data .= '],"count":"' . json_encode ( $total ) . '"}';
    echo $data;
} elseif ($action == 'getObject') {
    $total = 0;
    $condition = array ();
    if (! empty ( $_POST ['query'] ))
        $condition ['object_name'] = "N'%" . DB_escape_string ( $_POST ['query'] ) . "%'";

    $result = _single ( 'api/object' )->loadObjects ( null, $_POST ['limit'], $_POST ['start'], $total, $_SESSION ['UserID'], $_SESSION ['Password'], $condition );

    $data = '{"success":true';
    $data .= ',"data":[';
    $haveData = false;

    while ( $row = DB_fetch_array ( $result ) ) {
        $haveData = true;
        $data .= sprintf ( '{"object_id":"%s","object_number":"%s","object_name":"%s"},', $row ['object_id'], $row ['object_number'], $row ['object_name'] );
    }

    if ($haveData) {
        $data = substr ( $data, 0, strlen ( $data ) - 1 );
    }

    $data .= '],"count":"' . json_encode ( $total ) . '"}';
    echo $data;
} elseif ($action == 'getPartnerPrices') {
    $result = _single ( 'api/partner_prices' )->getAllPartnerPricesByPartnerId ( DB_escape_string ( trim ( $_POST ['partner_id'] ) ) );

    $data = '{"success":true';
    $data .= ',"data":[';
    $haveData = false;

    while ( $row = DB_fetch_array ( $result ) ) {
        $haveData = true;
        $data .= sprintf ( '{"id":"%s","price":"%s"},', $row ['id'], $row ['price'] );
    }

    if ($haveData) {
        $data = substr ( $data, 0, strlen ( $data ) - 1 );
    }

    $data .= '],"count":"' . json_encode ( $total ) . '"}';
    echo $data;
} elseif ($_GET ['action'] == 'export') {
    $DEFAULT_EXPORT_FILE_NAME = 'BienBanSanLuong';
    $DEFAULT_DIR_UPLOAD = 'tmp/exported/';
    $DEFAULT_EXCEL_TYPE = 'Excel5'; // default Excel 2003 - Excel5,Excel2007
    $Object = _single ( 'api/object' )->GetObjectById ( $_GET ['object'], $_SESSION ['UserID'], $_SESSION ['Password'] );
    $Partner = _single ( 'api/partner' )->GetPartnerById ( $_GET ['partnerId'], $_SESSION ['UserID'], $_SESSION ['Password'] );
    $titleObject = '';
    $titleDaidien = '';
    switch ($_GET ['type']) {
        case 1 :
            $titleObject = 'Ca sĩ';
            $titleDaidien = 'Xác nhận của ca sĩ';
            break;
        case 2 :
            $titleObject = 'Nhạc sĩ';
            $titleDaidien = 'Xác nhận của nhạc sĩ';
            break;
        default :
            break;
    }
    ;
    $docquyen = '';
    $ReleaseAuthority = null;
    if ($_GET ['release'] != - 1) {
        $ReleaseAuthority = $_GET ['release'];
        if ($_GET ['release'] == 1) {
            $docquyen = 'độcquyền';
        } else {
            $docquyen = 'khôngđộcquyền';
        }
    }
    // lay du lieu
    $partnerPriceObj = _single ( 'api/partner_prices' )->GetPartnerPriceById ( $_GET ['partnerPriceId'], $_SESSION ['UserID'], $_SESSION ['Password'] );
    $partnerPrice = $_GET ['partnerPrice'];
    $result = _single ( 'api/exchange' )->GetYieldRevenue ( $_GET ['partnerId'], $_GET ['partnerPrice'], $_GET ['object'], $_GET ['type'], $_GET ['fdate'], $_GET ['tdate'], null, null, $ReleaseAuthority, null, null, $total, $_SESSION ['UserID'], $_SESSION ['Password'] );
    $Objects = array ();
    while ( $row = DB_fetch_array ( $result ) ) {
        $Objects [$row ['exchange_imported_date']] [] = $row;
    }

    $TOTAL = 0;
    $ArrayCrossCheck = array ();
    foreach ( $Objects as $key => $value ) {
        $title .= $key . ',';
        $t = 0;
        for($i = 0; $i < count ( $value ); $i ++) {
            if ($_GET ['type'] == 1) {
                if ($value [$i] ['singer_id'] == $value [$i] ['composer_id']) {
                    $t += $value [$i] ['money_composer'];
                }
                $t += $value [$i] ['money_singer'] + $value [$i] ['money_release'];
            } elseif ($_GET ['type'] == 2) {
                $t += $value [$i] ['money_composer'];
            }
        }
        $ArrayCrossCheck [] = array (
                'title' => $key,
                'content' => $value [0] ['partner_name'],
                'quantity' => $t
        );
        $TOTAL += $t;
    }
    if (! empty ( $title )) {
        $title = substr ( $title, 0, strlen ( $title ) - 1 );
    }

    $DEFAULT_EXPORT_FILE_NAME .= '-' . str_replace ( ' ', '', $titleObject ) . '-' . str_replace ( ' ', '', $Object ['object_name'] ) . '-' . $title . '-' . $docquyen;

    $objPHPExcel = new Api_Excel ( $DEFAULT_EXPORT_FILE_NAME, $DEFAULT_DIR_UPLOAD, $DEFAULT_EXCEL_TYPE );
    $objPHPExcel->setActiveSheet ( 0 );

    $aligment_HorCent_VerCent = array (
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
    );

    $aligment_HorCent_VerBottom = array (
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
            'vertical' => PHPExcel_Style_Alignment::VERTICAL_BOTTOM
    );

    $border_thin = array (
            'style' => PHPExcel_Style_Border::BORDER_THIN
    );
    $border_thin_ALL_title = array (
            'borders' => array (
                    'top' => $border_thin,
                    'right' => $border_thin,
                    'bottom' => $border_thin,
                    'left' => $border_thin
            ),
            'alignment' => $aligment_HorCent_VerCent,
            'font' => array (
                    'bold' => true
            )
    );

    $border_thin_ALL = array (
            'borders' => array (
                    'top' => $border_thin,
                    'right' => $border_thin,
                    'bottom' => $border_thin,
                    'left' => $border_thin
            ),
            'alignment' => $aligment_HorCent_VerBottom,
            'font' => array (
                    'bold' => false,
                    'size' => 12
            )
    );

    // set default style
    $objPHPExcel->setPageSetup ( 85, PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4, PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT );
    $objPHPExcel->setDefaultStyle ( 'Times New Roman', 12 );
    // $objPHPExcel->setFreezePane("E13");
    $objPHPExcel->setPageMargins ( 0.5, 0.25, 0, 0.75 );
    $objPHPExcel->setHorizontalCentered ( true );

    $objPHPExcel->setColumnWidth ( "A", 2 )->setColumnWidth ( "B", 20 )->setColumnWidth ( "C", 40 )->setColumnWidth ( "D", 20 )->setColumnWidth ( "E", 21 );

    $objPHPExcel->mergeCells ( "B1", "E1" )->setText ( "B1", "CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM", false, false, false, 12 )->setCellHorizontalAllignment ( "B1", 'center' )->setRowHeight ( 1, 18 )->mergeCells ( "B2", "E2" )->setText ( "B2", "Độc Lập - Tự Do - Hạnh Phúc\n------------------o0o-------------------", false, false, false, 12 )->setCellHorizontalAllignment ( "B2", 'center' )->setWrapText ( "B2" )->setRowHeight ( 2, 30 )->setRowHeight ( 3, 7 )->

    mergeCells ( "B4", "E4" )->setText ( "B4", "BẢNG XÁC NHẬN SẢN LƯỢNG – DOANH THU", true, false, false, 12 )->setCellHorizontalAllignment ( "B4", 'center' )->mergeCells ( "B5", "E5" )->setText ( "B5", "DỊCH VỤ NHẠC CHUÔNG CHỜ", true, false, false, 12 )->setCellHorizontalAllignment ( "B5", 'center' )->

    mergeCells ( "B6", "E6" )->setText ( "B6", "Giữa Công Ty TNHH MTV TM DV QC Việt Giải Trí và $titleObject " . $Object ['object_name'], false, false, false, 12 )->setCellHorizontalAllignment ( "B6", 'center' )->mergeCells ( "B7", "E7" )->setText ( "B7", "Theo hợp đồng số :    " . $Object ['contract_number'], false, false, false, 12 )->setCellHorizontalAllignment ( "B7", 'center' )->setText ( "B9", "I. Số liệu thống kê", true, false, false, 12 )->setText ( "B10", $partnerPriceObj ['note'], true, false, false, 12 )->

    setText ( "B11", "Stt" )->setText ( "C11", "Tháng" )->setText ( "D11", "Nội dung dịch vụ" )->setText ( "E11", "Doanh thu" )->setDuplicateStyleArray ( $border_thin_ALL_title, "B11", "B11" )->setDuplicateStyleArray ( $border_thin_ALL_title, "C11", "C11" )->setDuplicateStyleArray ( $border_thin_ALL_title, "D11", "D11" )->setDuplicateStyleArray ( $border_thin_ALL_title, "E11", "E11" );
    // $Objects = array('1'=>1, '2'=>2, '3'=>3);
    // $Objects = array();
    $StartRow = $Position = 12;
    $EndRow = 12;
    $stt = 1;

    $cFormulaTongTien = "0";
    $cFormulaThue = "0";
    $cFormulaPhaitra = "0";
    if (! empty ( $Objects )) {
        foreach ( $ArrayCrossCheck as $value ) {
            $objPHPExcel->setText ( "B$Position", "$stt" )->setText ( "C$Position", "Tháng " . $value ['title'] )->setText ( "D$Position", $value ['content'] )->setDuplicateStyleArray ( $border_thin_ALL, "B$Position", "B$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "C$Position", "C$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "D$Position", "D$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "E$Position", "E$Position" )->setValueExplicit ( "E" . $Position, $value ['quantity'], PHPExcel_Cell_DataType::TYPE_NUMERIC )->setFormatCode ( "E" . $Position, '_(* #,##_);_(* (#,##);_(* "-"??_);_(@_)' )->setCellHorizontalAllignment ( "E" . $Position, "right" )->

            setCellHorizontalAllignment ( "B$Position", 'center' )->setCellHorizontalAllignment ( "D$Position", 'center' );
            $stt ++;
            $Position ++;
        }
        $EndRow = $Position - 1;
        $cFormulaTongTien = "=SUM(E$StartRow:E$EndRow)";
        $cFormulaThue = "=E$Position*10/100";
        $cFormulaPhaitra = "=E$Position-E" . ($Position + 1);
    }

    $objPHPExcel->setText ( "B$Position", "$stt" )->mergeCells ( "C$Position", "D$Position" )->setText ( "C$Position", "Tổng số tiền thanh toán" )->setDuplicateStyleArray ( $border_thin_ALL, "B$Position", "B$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "C$Position", "D$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "E$Position", "E$Position" )->setCellHorizontalAllignment ( "B$Position", 'center' )->setText ( "E$Position", "$cFormulaTongTien", true )->setFormatCode ( "E" . $Position, '_(* #,##_);_(* (#,##);_(* "-"??_);_(@_)' )->setCellHorizontalAllignment ( "E" . $Position, "right" );
    $stt ++;
    $Position ++;
    $objPHPExcel->setText ( "B$Position", "$stt" )->mergeCells ( "C$Position", "D$Position" )->setText ( "C$Position", "Thuế TNCN 10%" )->setDuplicateStyleArray ( $border_thin_ALL, "B$Position", "B$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "C$Position", "D$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "E$Position", "E$Position" )->setCellHorizontalAllignment ( "B$Position", 'center' )->setText ( "E$Position", "$cFormulaThue", true )->setFormatCode ( "E" . $Position, '_(* #,##_);_(* (#,##);_(* "-"??_);_(@_)' )->setCellHorizontalAllignment ( "E" . $Position, "right" );
    $stt ++;
    $Position ++;
    $objPHPExcel->setText ( "B$Position", "$stt" )->mergeCells ( "C$Position", "D$Position" )->setText ( "C$Position", "Tổng số tiền đã trừ thuế TNCN" )->setDuplicateStyleArray ( $border_thin_ALL, "B$Position", "B$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "C$Position", "D$Position" )->setDuplicateStyleArray ( $border_thin_ALL, "E$Position", "E$Position" )->setCellHorizontalAllignment ( "B$Position", 'center' )->setText ( "E$Position", "$cFormulaPhaitra", true )->setFormatCode ( "E" . $Position, '_(* #,##_);_(* (#,##);_(* "-"??_);_(@_)' )->setCellHorizontalAllignment ( "E" . $Position, "right" );
    $Position += 2;
    $SoTien = round ( $TOTAL - ($TOTAL * 10 / 100), 0 );

    $objPHPExcel->mergeCells ( "B$Position", "D$Position" )->setWrapText ( "B$Position" )->setText ( "B$Position", "Tổng số tiền Công Ty TNHH MTV TM DV QC Việt Giải Trí thanh toán cho $titleObject " . $Object ['object_name'] . " về dịch vụ nhạc chuông chờ trên mạng " . $Partner ['partner_name'] . " tháng $title là (VNĐ):" )
                //->setValueExplicit("E".$Position, $SoTien, PHPExcel_Cell_DataType::TYPE_NUMERIC)
                ->setText->
 ( "E$Position", "$cFormulaPhaitra", true )->setFormatCode ( "E" . $Position, '_(* #,##_);_(* (#,##);_(* "-"??_);_(@_)' )
                //->setCellHorizontalAllignment("E".$Position, "right")
                //->setText("E$Position","$SoTien", true, false, false, 12)
                ->setCellHorizontalAllignment->
 ( "E" . ($Position), 'right' )->setCellVerticalAllignment ( "E" . ($Position), 'top' )->setCellBold ( "E" . ($Position), true )->setText ( "B" . ($Position + 1), "Bằng chữ:" )->setText ( "C" . ($Position + 1), ucfirst ( convertNumberToString ( ( string ) $SoTien ) ), true, false, true, 12, false )->setRowHeight ( $Position, 32 )->setText ( "D" . ($Position + 2), "Tp HCM, ngày …..tháng …..năm " . date ( "Y" ) . ".", false, false, true, 12, false )->setText ( "B" . ($Position + 3), "Xác nhận của Công Ty TNHH MTV TM DV QC Việt Giải Trí", true, false, false, 12, false )->mergeCells ( "D" . ($Position + 3), "E" . ($Position + 3) )->setText ( "D" . ($Position + 3), "Xác nhận của ca sỹ", true, false, false, 12, false )->setCellHorizontalAllignment ( "D" . ($Position + 3), 'center' )->setText ( "B" . ($Position + 4), "P.Giám đốc" )->setCellHorizontalAllignment ( "B" . ($Position + 4), 'center' )->setText ( "C" . ($Position + 4), "P . Đối soát" )->setCellHorizontalAllignment ( "C" . ($Position + 4), 'center' )->setText ( "B" . ($Position + 10), "LÊ THỊ THỦY ANH", true )->setText ( "C" . ($Position + 10), "VÕ NGỌC MINH THY", true )->setCellHorizontalAllignment ( "B" . ($Position + 10), 'center' )->setCellHorizontalAllignment ( "C" . ($Position + 10), 'center' );
    $objPHPExcel->outputToBrowser ();
    exit ();
}
?>